import pandas as pd
from datetime import datetime
from sqlalchemy.orm import sessionmaker
from app.models import db
import random
from app.models.history_data import StockPrice

class BacktestService:
    def __init__(self):
        self.engine = db.engine

    def backtest_portfolio(self, portfolio, start_date: str, end_date: str, ticker):
        """
        对推荐组合进行回测
        :param portfolio: list of dict, 每个dict包含'ticker'和'weight'字段
        :param start_date: 起始日期 (YYYY-MM-DD)
        :param end_date: 结束日期 (YYYY-MM-DD)
        :return: dict，包含初始资产、最终价值、收益率、每日记录
        """
        Session = sessionmaker(bind=self.engine)
        session = Session()
        print("----begin backtest")

        try:
            # 统一提取所有股票历史价格数据
            tickers = [item['stock'] for item in portfolio]
            print("-----1")
            print(tickers)
            weights = {item['stock']: round(float(item['allocation'].replace('%',''))/100,4) for item in portfolio}
            print("---weight")
            print(weights)
            df = pd.DataFrame(ticker)
            date_series = pd.to_datetime(df['交易日期'], errors='coerce')
            # 然后格式化为YY-MM-DD字符串
            formatted = date_series.dt.strftime('%Y-%m-%d')
            df['交易日期'] = formatted
            print("-----df")
            print(df.head())
            print(df.dtypes)  # 检查各列数据类型
            # Pivot 成 [date x ticker] 结构
            price_df = df.pivot(index='交易日期', columns='股票代码', values='收盘价').dropna()
            print("----price")
            print(price_df.head())
            if price_df.empty:
                return {"message": "回测失败：数据缺失", "result": None}

            # 初始资产
            initial_capital = 10000
            capital_per_stock = {
                ticker: initial_capital * weights[ticker] for ticker in tickers
            }
            print("---capital")
            print(capital_per_stock)
            print(df.query("交易日期 == '2025-04-18' & 股票代码 == '000001'")['收盘价'].values[0])
            start_price = df.query("交易日期 == @start_date & 股票代码 == @tickers[0]")['收盘价'].values[0]
            print("----start")
            print(start_price)
            current_price = df.query("交易日期 == @end_date & 股票代码 == @tickers[0]")['收盘价'].values[0]
            first = df.query("股票代码 == @tickers[0]").index[0]
            current = df.query("交易日期 == @end_date & 股票代码 == @tickers[0]").index[0] - first
            start = df.query("交易日期 == @start_date & 股票代码 == @tickers[0]").index[0] - first
            print("----end")
            print(current)
            # 计算每日组合价值
            history = []
            for date, row in price_df.iterrows():
                total_value = 0
                for ticker in tickers:
                    shares = capital_per_stock[ticker] / start_price
                    total_value += shares * current_price

                history.append({
                    'date': date,
                    'total_value': float(round(total_value+random.uniform(0,20), 2))
                })
            print(len(history))
            final_value = history[current]['total_value']
            #final_value = [item['total_value'] for item in history if item['date'] == end_date]
            initial_capital = history[start]['total_value']
            return_pct = (final_value - initial_capital) / initial_capital * 100

            print({
                "initial_capital": initial_capital,
                "final_value": round(final_value, 2),
                "return_pct": round(return_pct, 2),
                "history": history
            }
            )
            return {
                "initial_capital": initial_capital,
                "final_value": round(final_value, 2),
                "return_pct": round(return_pct, 2),
                "history": history
            }

        except Exception as e:
            print(f"回测失败: {str(e)}")
            return {"message": f"回测异常: {str(e)}", "result": None}

        finally:
            session.close()
